﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.TransportSpecificServiceConfigOverWrite;

namespace BCData.TMS.TransportSpecificServiceConfigOverWrite
{
    public class TransportSpecificServiceConfigOverWriteData : ITransportSpecificServiceConfigOverWriteData
    {
        private readonly IDatabaseContext databaseContext;
        private readonly string QUERY = @"SELECT
		    TransportSpecificServiceConfigOverwriteId,
		    TransportSpecificServiceConfigId,
            VehicleTypeId,
            VehicleTypeName,
		    SpecificServiceType,
		    ServiceTitle,
            ShortName,
		    HasRemark,
		    ServiceRemark,
		    ServiceAmount,
		    HasUnit,
		    ServiceUnit,
		    Enabled,
		    ProvinceCode,
		    CityCode,
		    CountyCode,
		    AgentRecordId,
		    CreateAdminId,
		    CreateAdminName,
		    CreateTime,
		    UpdateTime,
		    EditAdminId,
		    EditAdminName
        FROM
            transportspecificserviceconfigoverwrite";



        #region 构造函数
        public TransportSpecificServiceConfigOverWriteData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新运输特殊服务费配置重写纪录
        /// </summary>
        /// <param name="transportSpecificServiceConfigOverWriteEntity"></param>
        /// <returns></returns>
        public async Task<TransportSpecificServiceConfigOverWriteEntity> InsertAsync(TransportSpecificServiceConfigOverWriteEntity entity)
        {
            return await this.databaseContext.Master.InsertAsync(entity);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改运输特殊服务费配置重写记录
        /// </summary>
        /// <param name="transportSpecificServiceConfigOverWriteEntity"></param>
        /// <returns></returns>
        public async Task<TransportSpecificServiceConfigOverWriteEntity> UpdateAsync(TransportSpecificServiceConfigOverWriteEntity entity)
        {
            return await this.databaseContext.Master.UpdateAsync(entity);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除运输特殊服务费配置重写记录
        /// </summary>
        /// <param name="transportSpecificServiceConfigOverWriteId"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(long transportSpecificServiceConfigOverWriteId)
        {
            string sql = "DELETE FROM transportspecificserviceconfigoverwrite WHERE TransportSpecificServiceConfigOverWriteId = ?transportSpecificServiceConfigOverWriteId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TransportSpecificServiceConfigOverWriteId", transportSpecificServiceConfigOverWriteId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除运输特殊服务费配置重写记录
        /// </summary>
        /// <param name="transportSpecificServiceConfigId"></param>
        /// <returns></returns>
        public async Task<bool> DeleteByFatherIdAsync(long transportSpecificServiceConfigId)
        {
            string sql = "DELETE FROM transportspecificserviceconfigoverwrite WHERE TransportSpecificServiceConfigId = ?transportSpecificServiceConfigId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TransportSpecificServiceConfigId", transportSpecificServiceConfigId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion



        #region GetById
        /// <summary>
        /// 获取运输特殊服务费配置重写记录
        /// </summary>
        /// <param name="transportSpecificServiceConfigOverWriteId"></param>
        /// <returns></returns>
        public async Task<TransportSpecificServiceConfigOverWriteEntity> GetEntityAsync(long transportSpecificServiceConfigOverWriteId)
        {
            string sql = QUERY + " WHERE  TransportSpecificServiceConfigOverWriteId = ?transportSpecificServiceConfigOverWriteId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TransportSpecificServiceConfigOverWriteId", transportSpecificServiceConfigOverWriteId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<TransportSpecificServiceConfigOverWriteEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询运输特殊服务费配置重写记录
        public async Task<(IEnumerable<TransportSpecificServiceConfigOverWriteEntity>, int)> GetListAsync(int? vehicleTypeId, string specificServiceType, string serviceTitle, string provinceCode, string cityCode, string countyCode, long agentRecordId, int pageIndex, int pageSize)
        {
            string sqlView = GetSqlStr();
            string sqlViewCount = GetSqlCountStr();
             
            //WHERE    A.SpecificServiceType = ?SpecificServiceType AND A.ServiceTitle LIKE ?ServiceTitle
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();

            if (vehicleTypeId.HasValue)
            {
                wheres.Add("B.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int64);
            }


            if (!string.IsNullOrWhiteSpace(specificServiceType))
            {
                wheres.Add("B.SpecificServiceType LIKE ?SpecificServiceType");
                parameters.Add("?SpecificServiceType", $"%{specificServiceType}%", DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(serviceTitle))
            {
                wheres.Add("B.ServiceTitle LIKE ?ServiceTitle ");
                parameters.Add("?ServiceTitle", $"%{serviceTitle}%", DbType.AnsiString);
            }


            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("B.ProvinceCode = ?ProvinceCode ");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("B.CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("B.CountyCode =?CountyCode");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }

            wheres.Add("AgentRecordId=?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);



            string sql = string.Format(@"{0} {2}  ORDER BY B.TransportSpecificServiceConfigId LIMIT {3},{4} ; {1} {2}",
                   sqlView,
                   sqlViewCount,
                  wheres.Count > 0 ? " AND " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize
                  );
            return await this.databaseContext.QueryMultipleAsync<TransportSpecificServiceConfigOverWriteEntity, int>(sql, parameters);
        }
        //获取SQL
        private string GetSqlStr()
        { 
                return @" SELECT 	
	                                B.TransportSpecificServiceConfigOverwriteId,	    
	                                B.TransportSpecificServiceConfigId,
                                    B.VehicleTypeId,
                                    B.VehicleTypeName,
	                                B.SpecificServiceType , 
	                                D.ServiceTypeName,
	                                B.ServiceTitle ,
                                    B.ShortName,
	                                B.HasRemark ,
	                                B.ServiceRemark  ,
	                                B.ServiceAmount  ,
	                                B.HasUnit ,
	                                B.ServiceUnit ,
	                                B.CreateAdminId, 
	                                B.CreateAdminName ,
	                                B.CreateTime,
	                                B.EditAdminId,
	                                B.EditAdminName,
	                                B.UpdateTime,
	                                B.Enabled ,
	                                B.AgentRecordId,
	                                C.ProvinceCode, 
	                                C.ProvinceName, 
	                                C.CityCode, 
	                                C.CityName, 
	                                C.CountyCode, 
	                                C.CountyName
                                FROM transportspecificserviceconfigoverwrite B   
                                INNER JOIN county C
                                ON B.ProvinceCode = C.ProvinceCode AND B.CityCode = C.CityCode AND B.CountyCode = C.CountyCode 
	                            INNER JOIN specificservicetype AS D
	                            ON 	B.SpecificServiceType = D.SpecificServiceType ";  
 
        }
        //获取SQLCount
        private string GetSqlCountStr()
        { 
                return @"SELECT 	 COUNT(1)
                                FROM transportspecificserviceconfigoverwrite B   
                                INNER JOIN county C
                                ON B.ProvinceCode = C.ProvinceCode AND B.CityCode = C.CityCode AND B.CountyCode = C.CountyCode
	                            INNER JOIN specificservicetype AS D
	                            ON 	B.SpecificServiceType = D.SpecificServiceType ";
 
        }
        #endregion


        #region GetListAsync 查询运输特殊服务费配置重写记录条数
        public async Task<int> GetCountAsync(int vehicleTypeId,string provinceCode, string cityCode, string countyCode, long agentRecordId, long transportSpecificServiceConfigId)
        {
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();

            wheres.Add("VehicleTypeId=?VehicleTypeId");
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int64);

            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode =?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode =?CountyCode");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }

            wheres.Add("AgentRecordId =?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);

            wheres.Add("TransportSpecificServiceConfigId =?TransportSpecificServiceConfigId");
            parameters.Add("?TransportSpecificServiceConfigId", transportSpecificServiceConfigId, DbType.Int64);

            string sql = string.Format(@" SELECT COUNT(1) FROM transportspecificserviceconfigoverwrite   {0} ",
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);

            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters);
        }
        #endregion


        #region GetListAsync 查询运输特殊服务费配置重写记录
        public async Task<IEnumerable<TransportSpecificServiceConfigOverWriteEntity>> GetOnlyListAsync(int? vehicleTypeId,string specificServiceType, string serviceTitle, string provinceCode, string cityCode, string countyCode, long agentRecordId)
        {
            string sqlView = @"
                                            SELECT
                                            A.TransportSpecificServiceConfigOverwriteId, 
                                            A.TransportSpecificServiceConfigId, 
                                            A.VehicleTypeId,
                                            A.VehicleTypeName,
                                            A.SpecificServiceType, 
                                            A.ServiceTitle, 
                                            A.ShortName,
                                            A.HasRemark, 
                                            A.ServiceRemark, 
                                            A.ServiceAmount, 
                                            A.HasUnit, 
                                            A.ServiceUnit, 
                                            A.Enabled, 
                                            A.ProvinceCode, 
                                            A.CityCode, 
                                            A.CountyCode, 
                                            A.AgentRecordId, 
                                            A.CreateAdminId, 
                                            A.CreateAdminName, 
                                            A.CreateTime, 
                                            A.UpdateTime, 
                                            A.EditAdminId, 
                                            A.EditAdminName, 
                                            B.ProvinceName, 
                                            B.CityName, 
                                            B.CountyName
                                            FROM
                                            transportspecificserviceconfigoverwrite AS A
                                            INNER JOIN
                                            county AS B
                                            ON 
                                            A.CityCode = B.CityCode AND
                                            A.CountyCode = B.CountyCode AND
                                            A.ProvinceCode = B.ProvinceCode ";
             
            //WHERE    A.SpecificServiceType = ?SpecificServiceType AND A.ServiceTitle LIKE ?ServiceTitle
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();

            if (!string.IsNullOrWhiteSpace(specificServiceType))
            {
                wheres.Add("A.SpecificServiceType LIKE ?SpecificServiceType");
                parameters.Add("?SpecificServiceType", $"%{specificServiceType.Trim()}%", DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(serviceTitle))
            {
                wheres.Add("A.ServiceTitle LIKE ?ServiceTitle ");
                parameters.Add("?ServiceTitle", $"%{serviceTitle.Trim()}%", DbType.AnsiString);
            }


            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode =?CountyCode");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }

            wheres.Add("AgentRecordId=?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);



            string sql = string.Format(@"{0} {1} ORDER BY A.TransportSpecificServiceConfigId ",
                   sqlView,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""
                  );
            return await this.databaseContext.Slave.QueryAsync<TransportSpecificServiceConfigOverWriteEntity>(sql, parameters);
        }
        #endregion


        #region 特殊服务类型是否已经被使用
        public async Task< bool> IsUsedSpecificServiceType(string specificServiceType)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = "SELECT COUNT(1) FROM TransportSpecificServiceConfigOverWrite a WHERE  SpecificServiceType = ?SpecificServiceType";
            parameters.Add("?SpecificServiceType", specificServiceType, DbType.AnsiString);

            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters)>0;
        }
        #endregion
    }
}
//接口



